﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

namespace Our.Umbraco.uDbCompare
{
    public partial class DocTypeInUse : System.Web.UI.UserControl
    {
        public bool UseRemoteConnectionString { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
                BindData();
        }

        public void BindData()
        {
            if (SessionVars.IsValidDbConnection(UseRemoteConnectionString))
            {
                DataTable docTypeTable = Db.QueryDb("select alias from cmsContentType c join umbracoNode n on c.nodeId = n.Id where c.MasterContentType is not null and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'",
                                                UseRemoteConnectionString);
                docTypeTable.DefaultView.Sort = "alias asc";
                DocTypeDropDownList.DataTextField = "alias";
                DocTypeDropDownList.DataValueField = "alias";
                DocTypeDropDownList.DataSource = docTypeTable;
                DocTypeDropDownList.DataBind();

                SearchButton.Enabled = true;
                DefiniteUnusedButton.Enabled = true;
                PossibleUnusedButton.Enabled = true;
            }
            else
            {
                SearchButton.Enabled = false;
                DefiniteUnusedButton.Enabled = false;
                PossibleUnusedButton.Enabled = false;
            }
        }

        protected void DefiniteUnusedButton_OnClick(object sender, EventArgs e)
        {
            DataTable matchDt = FindDefiniteUnusedDocTypes();
            DocTypeGridView.DataSource = matchDt;
            DocTypeGridView.DataBind();

            NoResultsLabel.Visible = (matchDt.Rows.Count == 0);
        }

        protected void PossibleUnusedButton_OnClick(object sender, EventArgs e)
        {
            DataTable matchDt = FindPossibleUnusedDocTypes();
            DocTypeGridView.DataSource = matchDt;
            DocTypeGridView.DataBind();

            NoResultsLabel.Visible = (matchDt.Rows.Count == 0);
        }
        
        private DataTable FindDefiniteUnusedDocTypes()
        {
            string docTypeQuery = @"select ct.alias as DocTypeAlias, ct.nodeId as DocTypeNodeId, 
		                                n.text as ContentNodeName, n.id as ContentNodeId, n.path as ContentNodePath,
                                        case when count(childCt.nodeid) > 0 then 1 else 0 end as HasDocTypeChildren
                                        from cmsContentType ct 
                                        join umbracoNode nObject on ct.nodeId = nObject.id and nObject.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'
			                            left outer join cmscontent c on ct.nodeid = c.contenttype
			                            left outer join umbracoNode n on c.nodeid = n.id
                                        left outer join cmsContentType childCt on childCt.masterContentType = ct.nodeId
			                            where n.id is null  
                                        group by ct.alias , ct.nodeId, n.text, n.id, n.path
                                        having count(childCt.nodeid) = 0
                                        ";
            DataTable dt = Db.QueryDb(docTypeQuery, UseRemoteConnectionString);

            return dt;
        }

        private DataTable FindPossibleUnusedDocTypes()
        {
            string docTypeQuery = @"select ct.alias as DocTypeAlias, ct.nodeId as DocTypeNodeId, 
		                                n.text as ContentNodeName, n.id as ContentNodeId, n.path as ContentNodePath,
                                        case when count(childCt.nodeid) > 0 then 1 else 0 end as HasDocTypeChildren
                                        from cmsContentType ct  
                                        join umbracoNode nObject on ct.nodeId = nObject.id and nObject.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'
			                            left outer join cmscontent c on ct.nodeid = c.contenttype
                                        left outer join cmsDocument d on c.nodeid = d.nodeid and (d.newest = 1 or d.published = 1)			                            
			                            left outer join umbracoNode n on d.nodeid = n.id
                                        left outer join cmsContentType childCt on childCt.masterContentType = ct.nodeId
			                            where (n.id is null or n.path like '-1,-20,%')
				                                and n.id not in 
				                                (
					                                select d.nodeId from cmsDocument dContains
					                                join umbracoNode nContains on dContains.nodeid = nContains.id
					                                join cmsContent cContains on dContains.nodeid = cContains.nodeid
					                                join cmsContentType ctContains on cContains.contentType = ctContains.nodeid
					                                 where ctContains.nodeid = ct.nodeId
						                                and (dContains.newest = 1 or dContains.published = 1)			                            
						                                and nContains.path not like '-1,-20,%'
				                                )
                                        group by ct.alias , ct.nodeId, n.text, n.id, n.path
                                        having count(childCt.nodeid) = 0
                                        ";
            DataTable dt = Db.QueryDb(docTypeQuery, UseRemoteConnectionString);

            return dt;
        }

        private DataTable FindDocType(string alias)
        {
            string docTypeQuery = @"select ct.alias as DocTypeAlias, ct.nodeId as DocTypeNodeId,  
		                                n.text as ContentNodeName, n.id as ContentNodeId, n.path as ContentNodePath,
                                        case when count(childCt.nodeid) > 0 then 1 else 0 end as HasDocTypeChildren
                                        from cmsContentType ct  
                                        join umbracoNode nObject on ct.nodeId = nObject.id and nObject.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'
			                            left outer join cmscontent c on ct.nodeid = c.contenttype
                                        left outer join cmsDocument d on c.nodeid = d.nodeid			                            
                                        left outer join umbracoNode n on d.nodeid = n.id
                                        left outer join cmsContentType childCt on childCt.masterContentType = ct.nodeId
			                            where ct.alias = ? and ( d.newest is null or d.newest = 1 )
                                        group by ct.alias , ct.nodeId, n.text, n.id, n.path, d.newest
                                        ";
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@DocTypeAlias", DocTypeDropDownList.SelectedValue);
            DataTable dt = Db.QueryDb(docTypeQuery, UseRemoteConnectionString, parameters);

            return dt;
        }


        protected void SearchButton_OnClick(object sender, EventArgs e)
        {
            DataTable matchDt = FindDocType(DocTypeDropDownList.SelectedValue);
            DocTypeGridView.DataSource = matchDt;
            DocTypeGridView.DataBind();

            NoResultsLabel.Visible = (matchDt.Rows.Count == 0);
        }

        protected void DocTypeGridView_OnRowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataRowView drv = e.Row.DataItem as DataRowView;

                int? docTypeNodeId = drv["DocTypeNodeId"] as Int32?;
                int? contentNodeId = drv["ContentNodeId"] as Int32?;

                uDbCompareUI.SetLinkCell(uDbCompareUI.GetCellFromColumnName(e.Row, "DocTypeNodeId"),
                    docTypeNodeId.ToString(), uDbCompare.GetLinkUrl(LinkItem.DocType, docTypeNodeId, UseRemoteConnectionString));
                uDbCompareUI.SetLinkCell(uDbCompareUI.GetCellFromColumnName(e.Row, "ContentNodeId"),
                     contentNodeId.ToString(), uDbCompare.GetLinkUrl(LinkItem.Content, contentNodeId, UseRemoteConnectionString));

                TableCell contentNodePathCell = uDbCompareUI.GetCellFromColumnName(e.Row, "ContentNodePath");
                if (contentNodePathCell.Text.StartsWith("-1,-20,"))
                    uDbCompareUI.SetCellColour(contentNodePathCell, DbCompareMatch.Missing);

            }
        }
    }
}